package com.yuanfeng.commoms.util.excel;

import com.google.common.collect.Lists;
import com.yuanfeng.commoms.util.secverifyapiutil.Base64Utils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

/**
 * @author Administrator
 */
public class ExcelsUtil {

    /**
     * 普通excel
     *
     * @param title
     * @param maps
     * @param mergeIndex
     * @return
     */
    public static String createExcel(String[] title,
                                     Map<String/* sheet名 */, List<Map<String/* 对应title的值 */, String>>> maps, int[] mergeIndex) {
        if (title.length == 0) {
            return null;
        }
        /* 初始化excel模板 */
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = null;

        int n = 0;
        /* 循环sheet页 */
        for (Map.Entry<String, List<Map<String/* 对应title的值 */, String>>> entry : maps.entrySet()) {
            /* 实例化sheet对象并且设置sheet名称，book对象 */
            try {
                sheet = workbook.createSheet();
                workbook.setSheetName(n, entry.getKey());
                workbook.setSelectedTab(0);
            } catch (Exception e) {
                e.printStackTrace();
            }
            /* 初始化head，填值标题行（第一行） */
            Row row0 = sheet.createRow(0);
            for (int i = 0; i < title.length; i++) {
                /* 创建单元格，指定类型 */
                Cell cell_1 = row0.createCell(i, Cell.CELL_TYPE_STRING);
                cell_1.setCellValue(title[i]);
            }
            /* 得到当前sheet下的数据集合 */
            List<Map<String/* 对应title的值 */, String>> list = entry.getValue();
            /* 遍历该数据集合 */
            List<PoiModel> poiModels = Lists.newArrayList();
            if (null != workbook) {
                Iterator iterator = list.iterator();
                int index = 1;/* 这里1是从excel的第二行开始，第一行已经塞入标题了 */
                while (iterator.hasNext()) {
                    Row row = sheet.createRow(index);
                    /* 取得当前这行的map，该map中以key，value的形式存着这一行值 */
                    Map<String, String> map = (Map<String, String>) iterator.next();
                    /* 循环列数，给当前行塞值 */
                    for (int i = 0; i < title.length; i++) {
                        String old = "";
                        /* old存的是上一行统一位置的单元的值，第一行是最上一行了，所以从第二行开始记 */
                        if (index > 1) {
                            old = poiModels.get(i) == null ? "" : poiModels.get(i).getContent();
                        }
                        /* 循环需要合并的列 */
                        for (int j = 0; j < mergeIndex.length; j++) {
                            if (index == 1) {
                                /* 记录第一行的开始行和开始列 */
                                PoiModel poiModel = new PoiModel();
                                poiModel.setOldContent(map.get(title[i]));
                                poiModel.setContent(map.get(title[i]));
                                poiModel.setRowIndex(1);
                                poiModel.setCellIndex(i);
                                poiModels.add(poiModel);
                                break;
                            } else if (i > 0 && mergeIndex[j] == i) {/* 这边i>0也是因为第一列已经是最前一列了，只能从第二列开始 */
                                /* 当前同一列的内容与上一行同一列不同时，把那以上的合并, 或者在当前元素一样的情况下，前一列的元素并不一样，这种情况也合并 */
                                /*
                                 * 如果不需要考虑当前行与上一行内容相同，但是它们的前一列内容不一样则不合并的情况，把下面条件中||poiModels.get(i).getContent()
                                 * .equals(map.get(title[i])) && !poiModels.get(i -
                                 * 1).getOldContent().equals(map.get(title[i-1]))去掉就行
                                 */
                                if (!poiModels.get(i).getContent().equals(map.get(title[i])) || poiModels.get(i)
                                        .getContent().equals(map.get(title[i]))
                                        && null!=poiModels.get(i - 1).getOldContent()&&!poiModels.get(i - 1).getOldContent().equals(map.get(title[i - 1]))) {
                                    /* 当前行的当前列与上一行的当前列的内容不一致时，则把当前行以上的合并 */
                                    CellRangeAddress cra = new CellRangeAddress(
                                            poiModels.get(i).getRowIndex()/* 从第二行开始 */, index - 1/* 到第几行 */,
                                            poiModels.get(i).getCellIndex()/* 从某一列开始 */,
                                            poiModels.get(i).getCellIndex()/* 到第几列 */);
                                    // 在sheet里增加合并单元格
//									sheet.addMergedRegion(cra);
                                    /* 重新记录该列的内容为当前内容，行标记改为当前行标记，列标记则为当前列 */
                                    poiModels.get(i).setContent(map.get(title[i]));
                                    poiModels.get(i).setRowIndex(index);
                                    poiModels.get(i).setCellIndex(i);
                                }

                            }

                            /* 处理第一列的情况 */
                            if (mergeIndex[j] == i && i == 0
                                    && !poiModels.get(i).getContent().equals(map.get(title[i]))) {
                                /* 当前行的当前列与上一行的当前列的内容不一致时，则把当前行以上的合并 */
                                CellRangeAddress cra = new CellRangeAddress(poiModels.get(i).getRowIndex()/* 从第二行开始 */,
                                        index - 1/* 到第几行 */, poiModels.get(i).getCellIndex()/* 从某一列开始 */,
                                        poiModels.get(i).getCellIndex()/* 到第几列 */);
                                // 在sheet里增加合并单元格
//								sheet.addMergedRegion(cra);
                                /* 重新记录该列的内容为当前内容，行标记改为当前行标记 */

                                poiModels.get(i).setContent(map.get(title[i]));
                                poiModels.get(i).setRowIndex(index);
                                poiModels.get(i).setCellIndex(i);
                            }

                            /* 最后一行没有后续的行与之比较，所有当到最后一行时则直接合并对应列的相同内容 */
                            if (mergeIndex[j] == i && index == list.size()) {
                                System.out.println(6);
                                CellRangeAddress cra = new CellRangeAddress(poiModels.get(i).getRowIndex()/* 从第二行开始 */,
                                        index/* 到第几行 */, poiModels.get(i).getCellIndex()/* 从某一列开始 */,
                                        poiModels.get(i).getCellIndex()/* 到第几列 */);
                                // 在sheet里增加合并单元格
//								sheet.addMergedRegion(cra);
                            }
                        }

                        Cell cell = row.createCell(i, Cell.CELL_TYPE_STRING);
                        cell.setCellValue(map.get(title[i]));
                        /* 在每一个单元格处理完成后，把这个单元格内容设置为old内容 */
                        poiModels.get(i).setOldContent(old);
                    }
                    index++;
                }
            }
            n++;
        }
        /* 生成临时文件 */
        String fileName = String.valueOf(System.currentTimeMillis() / 1000);
        return getFileBase64URLString(workbook, fileName);
    }

    /**
     * 账单excel
     *
     * @param title
     * @param head
     * @param footer1
     * @param footer2
     * @param maps
     * @return
     */
    public static String generateExcelOfAccount(String[] title, String[] head, String[] footer1, String[] footer2, Map<String, List<Map<String, String>>> maps) {
        /* 初始化excel模板 */
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = null;
        int headSize = head.length;
        int footer1Size = footer1.length;
        int footer2Size = footer2.length;
        int n = 0;
        /* 循环sheet页 */
        String fileName = "";
        for (Map.Entry<String, List<Map<String/* 对应title的值 */, String>>> entry : maps.entrySet()) {
            /* 实例化sheet对象并且设置sheet名称，book对象 */
            try {
                sheet = workbook.createSheet();
                workbook.setSheetName(n, entry.getKey());
                workbook.setSelectedTab(0);
            } catch (Exception e) {
                e.printStackTrace();
            }
            if (maps.size() == 1 || n == 0) {
                fileName = entry.getKey();
            } else if (n == maps.size() - 1) {
                fileName = fileName + "~" + entry.getKey();
            }
            /* 得到当前sheet下的数据集合 */
            List<Map<String/* 对应title的值 */, String>> list = entry.getValue();
            /* 遍历该数据集合 */
            List<PoiModel> poiModels = Lists.newArrayList();
            if (null != workbook) {
                Iterator iterator = list.iterator();
                int index = 0;/* 这里1是从excel的第二行开始，第一行已经塞入标题了 */
                while (iterator.hasNext()) {
                    Row row = sheet.createRow(index);
                    /* 取得当前这行的map，该map中以key，value的形式存着这一行值 */
                    Map<String, String> map = (Map<String, String>) iterator.next();
                    Integer length = Integer.parseInt(map.get("length"));
                    /* 循环列数，给当前行塞值 */
                    for (int i = 0; i < title.length; i++) {
                        String old = "";
                        Boolean flag = true;
                        /* old存的是上一行统一位置的单元的值，第一行是最上一行了，所以从第二行开始记 */
                        if (index > 1 && index < (length + 2)) {
                            old = poiModels.get(i) == null ? "" : poiModels.get(i).getContent();
                        }
                        /* 循环需要合并的列 */
                        int[] mergeIndex = new int[]{0};
                        for (int j = 0; j < mergeIndex.length; j++) {
                            PoiModel poiModel = new PoiModel();
                            if (index == 0) {
                                if (i < headSize) {
                                    Integer headCell = 0;
                                    if (i != 0) {
                                        headCell = (i * 2);
                                    }
                                    CellRangeAddress cra = new CellRangeAddress(
                                            0/* 从第二行开始 */, 0/* 到第几行 */,
                                            headCell/* 从某一列开始 */,
                                            headCell + 1/* 到第几列 */);
                                    sheet.addMergedRegion(cra);
                                    /* 记录第一行的开始行和开始列 */
                                    setCell(poiModel, i, head, poiModels, index, row, headCell, old, map);
                                }
                                break;
                            } else if (index == 1) {
                                Cell cell_1 = row.createCell(i, Cell.CELL_TYPE_STRING);
                                cell_1.setCellValue(title[i]);
                                break;
                            } else if (index > 1 && index < (length + 2)) {
                                setCell(poiModel, i, title, poiModels, index, row, i, old, map);
                                break;
                            } else if (index == (length + 2)) {
                                setFootRow(footer1, sheet, footer1Size, poiModels, index, row, map, i, old, poiModel);
                                break;
                            } else {
                                setFootRow(footer2, sheet, footer2Size, poiModels, index, row, map, i, old, poiModel);
                                break;
                            }
                        }
                    }
                    index++;
                }
            }
            n++;
        }
        /* 生成临时文件 */
        return getFileBase64URLString(workbook, fileName);
    }

    /**
     * 读取excel
     *
     * @param file
     * @return
     */
    public static List<Map<Object, Object>> readExcel(MultipartFile file) {
        List<Map<Object, Object>> list = new ArrayList<>();
        try {
            Workbook wookbook = new XSSFWorkbook(file.getInputStream());
            Sheet sheet = wookbook.getSheetAt(0);
            int rows = sheet.getPhysicalNumberOfRows();
            for (int i = 1; i < rows; i++) {
                Row row = sheet.getRow(i);
                Map<Object, Object> map = new HashMap<>();
                if (null == row || row.getPhysicalNumberOfCells() == 0) {
                    continue;
                }
                Cell orderId = row.getCell(1);
                if (null != orderId) {
                    map.put("orderId", getValue(orderId));
                }
                Cell goodsId = row.getCell(13);
                if (null != goodsId) {
                    map.put("goodsId", getValue(goodsId));
                }
                Cell orderShippingExpressId = row.getCell(6);
                if (null != orderId) {
                    map.put("orderShippingExpressId", getValue(orderShippingExpressId));
                }
                Cell orderShippingCode = row.getCell(8);
                if (null != orderId) {
                    map.put("orderShippingCode", getValue(orderShippingCode));
                }
                Cell orderSellerName = row.getCell(17);
                if (null != orderId) {
                    map.put("orderSellerName", getValue(orderSellerName));
                }
                Cell orderSellerAddress = row.getCell(18);
                if (null != orderId) {
                    map.put("orderSellerAddress", getValue(orderSellerAddress));
                }
                Cell orderSellerContact = row.getCell(19);
                if (null != orderId) {
                    map.put("orderSellerContact", getValue(orderSellerContact));
                }
                Cell shippingAddressId = row.getCell(20);
                if (null != orderId) {
                    map.put("shippingAddressId", getValue(shippingAddressId));
                }
                list.add(map);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 获取数据类型
     *
     * @param xSSFCell
     * @return
     */
    public static String getValue(Cell xSSFCell) {
        if (null == xSSFCell) {
            return "";
        }
        String cellValue = "";
        // 以下是判断数据的类型
        switch (xSSFCell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                xSSFCell.setCellType(Cell.CELL_TYPE_STRING);
                cellValue = xSSFCell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_STRING: // 字符串
                cellValue = xSSFCell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                cellValue = xSSFCell.getBooleanCellValue() + "";
                break;
            case HSSFCell.CELL_TYPE_FORMULA: // 公式
                cellValue = xSSFCell.getCellFormula() + "";
                break;
            case HSSFCell.CELL_TYPE_BLANK: // 空值
            case HSSFCell.CELL_TYPE_ERROR: // 故障
            default:
                cellValue = "";
                break;
        }
        return cellValue;
    }

    /**
     * 判断字符串是否为URL
     * @param urls 用户头像key
     * @return true:是URL、false:不是URL
     */
    public static boolean isHttpUrl(String urls) {
        boolean isurl = false;
        if(StringUtils.isNotEmpty(urls)) {
            if (urls.startsWith("http://") || urls.startsWith("https://")) {
                isurl = true;
            }
        }
        return isurl;
    }


    public static boolean isHttpUrl(Cell cell) {
        if(null != cell) {
            String path = getValue(cell);
            if(StringUtils.isNotEmpty(path)) {
                return isHttpUrl(path);
            }
        }
        return true;

    }


    /**
     * 返回根据文件生成的url地址
     *
     * @param workbook
     * @return
     */
    public static String getFileBase64URLString(Workbook workbook, String fileName) {
        FileOutputStream out = null;
        String url = null;
        try {
            File tempFile = File.createTempFile(fileName, ".xlsx");
            out = new FileOutputStream(tempFile);
            workbook.write(out);
            url = Base64Utils.fileToBase64(tempFile);
            System.out.println(url);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                out.flush();
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
            return url;
        }
    }
    private static void setFootRow(String[] footer2, Sheet sheet, int footer2Size, List<PoiModel> poiModels, int index, Row row, Map<String, String> map, int i, String old, PoiModel poiModel) {
        if (i < footer2Size) {
            Integer headCell1 = 0;
            Integer headCell2 = 0;
            if (i == 0) {
                headCell1 = 0;
                headCell2 = 1;
            } else {
                headCell1 = 2;
                headCell2 = 7;
            }
            CellRangeAddress cra = new CellRangeAddress(
                    index/* 从第二行开始 */, index/* 到第几行 */,
                    headCell1/* 从某一列开始 */,
                    headCell2/* 到第几列 */);
            sheet.addMergedRegion(cra);
            /* 记录第一行的开始行和开始列 */
            poiModel.setOldContent(map.get(footer2[i]));
            poiModel.setContent(map.get(footer2[i]));
            poiModel.setRowIndex(index);
            poiModel.setCellIndex(i);
            poiModels.add(poiModel);
            Cell cell = row.createCell(headCell1, Cell.CELL_TYPE_STRING);
            cell.setCellValue(map.get(footer2[i]));
            /* 在每一个单元格处理完成后，把这个单元格内容设置为old内容 */
            poiModels.get(i).setOldContent(old);
        }
    }
    private static void setCell(PoiModel poiModel, int i, String[] list, List<PoiModel> poiModels, int index, Row row, int headCell, String old, Map<String, String> map) {
        poiModel.setOldContent(map.get(list[i]));
        poiModel.setContent(map.get(list[i]));
        poiModel.setRowIndex(index);
        poiModel.setCellIndex(i);
        poiModels.add(poiModel);
        Cell cell = row.createCell(headCell, Cell.CELL_TYPE_STRING);
        cell.setCellValue(map.get(list[i]));
        /* 在每一个单元格处理完成后，把这个单元格内容设置为old内容 */
        poiModels.get(i).setOldContent(old);
    }


    /**
     * 检查Excel 主图
     *
     * @param sheet
     * @return
     */
    public static List<String> checkMainImages(Sheet sheet) {
        int rows = sheet.getPhysicalNumberOfRows();
        Map<String, String> mainGoodsImages = new HashMap<String, String>();
        // 主图重复，后 存在codeNumber
        List<String> errorMainImages = new ArrayList<String>();
        // 解析的编码
        List<String> codeNumbers = new ArrayList<String>();
        // 检查主图的唯一性
        for (int i = 1; i < rows; i++) {
            Row row = sheet.getRow(i);
            if (null != row && row.getPhysicalNumberOfCells() > 0) {
                Cell goodsImagesCell1 = row.getCell(17);
                Cell codeNumberCell = row.getCell(0);
                if (null != goodsImagesCell1 && null != codeNumberCell) {
                    String codeNumber = getValue(codeNumberCell);
                    String mainGoodsImage = getValue(goodsImagesCell1);
                    // 编码不存在，说明是第一次解析到
                    if (!codeNumbers.contains(codeNumber)) {
                        codeNumbers.add(codeNumber);
                        if (StringUtils.isNotEmpty(mainGoodsImage)) {
                            // 主图名字已经存在
                            if (mainGoodsImages.containsKey(mainGoodsImage)) {
                                errorMainImages.add(codeNumber);
                                errorMainImages.add(mainGoodsImages.get(mainGoodsImage));
                            } else {
                                mainGoodsImages.put(mainGoodsImage, codeNumber);
                            }
                        }
                    }
                }
            }
        }
        return errorMainImages;
    }

    /**
     * 校验 相同编码 shopId是否相同
     *
     * @param sheet
     * @return 返回错误的编码集合
     */
    public static List<String> checkShopId(Sheet sheet) {
        int rows = sheet.getPhysicalNumberOfRows();
        // 用来存储错误的shopId的编码
        List<String> errorShopIds = new ArrayList<String>();
        // 存储codeNumber 和 shopId 的对应关系
        Map<String, String> codeNumberShopIds = new HashMap<>();
        for (int index = 1; index < rows; index++) {
            Row row = sheet.getRow(index);
            if (null != row && row.getPhysicalNumberOfCells() > 0) {
                Cell shopIdCell = row.getCell(1);
                Cell codeNumberCell = row.getCell(0);
                if (null != shopIdCell && null != codeNumberCell) {
                    String codeNumber = getValue(codeNumberCell);
                    String shopId = getValue(shopIdCell);
                    if (StringUtils.isNotEmpty(shopId) && StringUtils.isNotEmpty(codeNumber)) {
                        if (codeNumberShopIds.containsKey(codeNumber)) {
                            String shopIdMap = codeNumberShopIds.get(codeNumber);
                            if (!shopIdMap.equals(shopId)) {
                                errorShopIds.add(codeNumber);
                            }
                        } else {
                            codeNumberShopIds.put(codeNumber, shopId);
                        }
                    }
                }
            }
        }
        return errorShopIds;
    }

    /**
     * 校验规格是否相同，不相同返回编码
     */
    public static List<String> checkSpec(Sheet sheet) {
        int rows = sheet.getPhysicalNumberOfRows();
        // 用来存储不相同的规格类型的编码
        List<String> errorSpeces = new ArrayList<String>();
        // 存储codeNumber 和 spec规格 的对应关系
        Map<String, String> codeNumberSpeces = new HashMap<>();
        for (int index = 1; index < rows; index++) {
            Row row = sheet.getRow(index);
            if (null != row && row.getPhysicalNumberOfCells() > 0) {
                Cell codeNumberCell = row.getCell(0);
                Cell specCell = row.getCell(13);
                if (null != codeNumberCell && null != specCell) {
                    String codeNumber = getValue(codeNumberCell);
                    String specStr = getValue(specCell);
                    if (StringUtils.isNotEmpty(specStr) && StringUtils.isNotEmpty(codeNumber)) {
                        if (codeNumberSpeces.containsKey(codeNumber)) {
                            String oldSpec = codeNumberSpeces.get(codeNumber);
                            if (!specStr.equals(oldSpec)) {
                                errorSpeces.add(codeNumber);
                            }
                        }
                    } else {
                        codeNumberSpeces.put(codeNumber, specStr);
                    }
                }
            }
        }
        return errorSpeces;
    }




}
